create database HOUSE_DB
on
(
	name = 'HOUSE_DB',
	filename = 'D:\HOUSE_DB.mdf',
	size = 5,
	maxsize= 50,
	filegrowth = 10%
)
log on 
(
	name= 'HOUSE_DB_log',
	filename = 'D:\HOUSE_DB_log.ldf',
	size = 5,
	maxsize= 50,
	filegrowth = 10% 
)
use HOUSE_DB
go

create table HOUSE_TYPE
(
	type_id int primary key identity,
	type_name varchar(50) unique
)
create table HOUSE
(
	house_id int primary key identity ,
	house_name varchar(50),
	house_price float default (0),
	type_id int foreign key references HOUSE_TYPE(type_id)
)
go
insert into HOUSE_TYPE(type_name)
select '小户型' union select '经济型' union select '别墅' 

insert into HOUSE(house_name,house_price,type_id)
values('鱼',40,1),('龟',45,2),('虾',60,3)
--go
--查询所有房屋信息
select * from HOUSE
--使用模糊查询包含”型“字的房屋类型信息
select * from HOUSE inner join HOUSE_TYPE on HOUSE_TYPE.type_id = HOUSE.type_id
where type_name like '%型%'
--查询出房屋的名称和租金，并且按照租金降序排序
select house_name,house_price from HOUSE order by house_price desc
--使用连接查询，查询信息，显示房屋名称和房屋类型名称
select house_name,type_name from HOUSE inner join HOUSE_TYPE on HOUSE_TYPE.type_id = HOUSE.type_id
--查询所有房屋中月租最高的房屋，显示最高的租金和房屋名称
select house_price,house_name from HOUSE where house_price = (select max(house_price) from HOUSE )  